﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using KingRoad.EPM.Web.Areas.PersonnelLog.ViewModels;

using System.Dynamic;
using KingRoad.EPM.PersonnelLogEntity.Model;
using KingRoad.EPM.Web.Areas.PersonnelLog.PersonnelLogFramework.Common;
using System.IO;
using System.Data;
using System.Text;
using System.Net;
using System.Text.RegularExpressions;
using System.Web.Routing;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace KingRoad.EPM.Web.Areas.PersonnelLog.Controllers
{
    public class PlmPersonnelAttendanceController : BaseController
    {
        public const string RYGL_SGRY = "RYGL_RYKQ";
        //
        // GET: /PlmPersonnelattendance/
        /// <summary>
        /// 总监办考勤-首页
        /// </summary>
        /// <returns></returns>
        public ActionResult SupervisionAttendanceIndex()
        {
            return View();
        }
        /// <summary>
        /// 驻地办考勤-首页
        /// </summary>
        /// <returns></returns>
        public ActionResult SupervisingAttendanceIndex()
        {
            return View();
        }
        /// <summary>
        /// 中心实验室-首页
        /// </summary>
        /// <returns></returns>
        public ActionResult VerifyAttendanceIndex()
        {
            return View();
        }
        /// <summary>
        /// 人员考勤-列表
        /// </summary>
        /// <returns></returns>
        public ActionResult PersonnelAttendanceList(string Section, string type)
        {
            // var puu = ProjectUnitUIRule.GetUnitInfoByCode(Section);
            ViewBag.Section = Section;
            ViewBag.type = type;
            ViewBag.attendtime = DateTime.Now.AddMonths(-1).ToString("yyyy-MM");
            //ViewBag.danwei = puu.UNIT_NAME;
            int totalRecord = 0;
            //当前页码
            ViewBag.pageNum = 1;
            //每页多少条
            ViewBag.numPerPage = 20;
            var List = PlmPersonnelattendanceUIRule.GetListForPage(ViewBag.pageNum, ViewBag.numPerPage, "PA_Date", out totalRecord, " PA_UnitCode='" + Section + "' and  Convert(varchar,PA_Date,120) like '" + ViewBag.attendtime + "%'  ");

            int tempTotalRecord = 0;
            var allList = PlmPersonnelattendanceUIRule.GetListForPage(1, ALL_RECORD_ITEMS, "PA_Date", out tempTotalRecord, " PA_UnitCode='" + Section + "' and  Convert(varchar,PA_Date,120) like '" + ViewBag.attendtime + "%'  ");
            SetSectionValue(RYGL_SGRY, allList);



            //总条数
            ViewBag.totalcount = totalRecord;
            //总页数
            ViewBag.pagenumshown = totalRecord % ViewBag.numPerPage == 0 ? totalRecord / ViewBag.numPerPage : totalRecord / ViewBag.numPerPage + 1;
            return View(new PlmPersonnelAttendanceViewModel { PersonnelAttendanceList = List });
        }
        /// <summary>
        /// 人员考勤-列表(分页提交)
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult SplitPage()
        {
            int totalRecord = 0;
            //当前页码
            ViewBag.pageNum = Convert.ToInt32(Request.Form["pageNum"]);
            //每页多少条
            ViewBag.numPerPage = Convert.ToInt32(Request.Form["numPerPage"]);
            ViewBag.attendtime = Request.Form["attendtime"];
            ViewBag.attendname = Request.Form["attendname"];
            ViewBag.type = Request.Form["input_type"];
            ViewBag.Section = Request.Form["input_section"];
            //var puu = ProjectUnitUIRule.GetUnitInfoByCode(ViewBag.Section);
            //ViewBag.danwei = puu.UNIT_NAME;
            string sql = " 1=1 ";
            if (ViewBag.attendtime != "")
                sql += " and Convert(varchar,PA_Date,120) like '" + ViewBag.attendtime + "%' ";
            if (ViewBag.attendname != "")
                sql += " and PA_PersonnelName like '%" + ViewBag.attendname + "%' ";
            if (ViewBag.Section != "")
                sql += " and PA_UnitCode = '" + ViewBag.Section + "' ";

            var List = PlmPersonnelattendanceUIRule.GetListForPage(ViewBag.pageNum, ViewBag.numPerPage, "PA_Date", out totalRecord, sql);

            int tempTotalRecord = 0;
            var allList = PlmPersonnelattendanceUIRule.GetListForPage(1, ALL_RECORD_ITEMS, "PA_Date", out tempTotalRecord, sql);
            SetSectionValue(RYGL_SGRY, allList);

            //总条数
            ViewBag.totalcount = totalRecord;
            //总页数
            ViewBag.pagenumshown = totalRecord % ViewBag.numPerPage == 0 ? totalRecord / ViewBag.numPerPage : totalRecord / ViewBag.numPerPage + 1;
            return View("PersonnelAttendanceList", new PlmPersonnelAttendanceViewModel { PersonnelAttendanceList = List });
        }
        /// <summary>
        /// 人员考勤-编辑
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult PersonnelAttendanceEdit(Guid id)
        {
            var Info = PlmPersonnelattendanceUIRule.GetInfoByID(id);
            return View(new PlmPersonnelAttendanceViewModel { PersonnelAttendancelInfo = Info });
        }
        /// <summary>
        /// 人员考勤-编辑（保存工作） 
        /// 功能：执行保存数据
        /// </summary>
        /// <returns></returns>
        public JsonResult PersonnelAttendanceEditData(PlmPersonnelAttendanceViewModel vm)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                bool safe = PlmPersonnelattendanceUIRule.UpdateEntity(vm.PersonnelAttendancelInfo);
            });

            dynamic jsonReturn = new ExpandoObject();

            //保存成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "更新成功",
                    navTabId = "tzgl_tzsb_list",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "更新失败",
                };
            }

            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 人员考勤——查看
        /// </summary>
        /// <returns></returns>
        public ActionResult PersonnelAttendanceDetail(Guid id)
        {
            var Info = PlmPersonnelattendanceUIRule.GetInfoByID(id);
            return View(new PlmPersonnelAttendanceViewModel { PersonnelAttendancelInfo = Info });
        }
        #region Excel导入、导出
        /// <summary>
        /// Excel导出
        /// </summary>
        ///// <returns></returns>
        public FileResult Excel(string Section, string Date)
        {
            // var list = PlmPersonnelattendanceUIRule.GetPersonnelAttendanceBySectionAndDate(Section, Date);
            var list = GetSessionValue<List<PlmPersonnelattendanceInfo>>(RYGL_SGRY);
            //创建Excel文件的对象
            IWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            //列宽度
            sheet1.SetColumnWidth(0, 5 * 256);
            sheet1.SetColumnWidth(1, 15 * 256);
            sheet1.SetColumnWidth(2, 18 * 256);
            sheet1.SetColumnWidth(3, 13 * 256);
            sheet1.SetColumnWidth(4, 15 * 256);
            sheet1.SetColumnWidth(5, 13 * 256);
            sheet1.SetColumnWidth(6, 30 * 256);
            sheet1.SetColumnWidth(7, 13 * 256);
            //设置第一行
            IRow row0 = sheet1.CreateRow(0);
            //行高
            row0.Height = 30 * 20;
            //合并单元格
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
            //创建单元格
            if (list.Count > 0)
            {
                CreateCell(row0, 0, Getcellstyle(book, "center", false, 14, 300), list[0].PA_UnitCode.TrimEnd() + "标" + list[0].PA_Date.ToString("yyy年MM月") + "考勤汇总表");
            }
            else
            {
                CreateCell(row0, 0, Getcellstyle(book, "center", false, 14, 300), "考勤汇总表");
            }

            //第二行
            IRow row1 = sheet1.CreateRow(1);
            //行高
            row1.Height = 25 * 20;
            CreateCell(row1, 0, Getcellstyle(book, "center", true, 11, 600), "序号");
            CreateCell(row1, 1, Getcellstyle(book, "center", true, 11, 600), "姓名");
            CreateCell(row1, 2, Getcellstyle(book, "center", true, 11, 600), "职务");
            CreateCell(row1, 3, Getcellstyle(book, "center", true, 11, 600), "应出勤天数");
            CreateCell(row1, 4, Getcellstyle(book, "center", true, 11, 600), "出勤天数");
            CreateCell(row1, 5, Getcellstyle(book, "center", true, 11, 600), "缺勤天数");
            CreateCell(row1, 6, Getcellstyle(book, "center", true, 11, 600), "罚款金额");
            CreateCell(row1, 7, Getcellstyle(book, "center", true, 11, 600), "备注");
            //将数据逐步写入sheet1各个行
            decimal fine = 0;
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 2);
                CreateCell(rowtemp, 0, Getcellstyle(book), (i + 1).ToString());
                CreateCell(rowtemp, 1, Getcellstyle(book), list[i].PA_PersonnelName);
                CreateCell(rowtemp, 2, Getcellstyle(book), list[i].PA_Post);
                CreateCell(rowtemp, 3, Getcellstyle(book), list[i].PA_WorkDay.ToString());
                CreateCell(rowtemp, 4, Getcellstyle(book), list[i].PA_AttendanceDay.ToString());
                CreateCell(rowtemp, 5, Getcellstyle(book), list[i].PA_AbsenceDay.ToString());
                CreateCell(rowtemp, 6, Getcellstyle(book), list[i].PA_Fine.ToString());
                CreateCell(rowtemp, 7, Getcellstyle(book), list[i].PA_Remark);

                fine += list[i].PA_Fine;
                if (i == list.Count - 1)
                {
                    IRow row2 = sheet1.CreateRow(i + 3);
                    row2.Height = 27 * 20;
                    CreateCell(row2, 0, Getcellstyle(book, "center", true, 11, 600), "合计");
                    CreateCell(row2, 1, Getcellstyle(book, "center", true, 11, 600), "");
                    CreateCell(row2, 2, Getcellstyle(book, "center", true, 11, 600), "");
                    CreateCell(row2, 3, Getcellstyle(book, "center", true, 11, 600), "");
                    CreateCell(row2, 4, Getcellstyle(book, "center", true, 11, 600), "");
                    CreateCell(row2, 5, Getcellstyle(book, "center", true, 11, 600), "");
                    CreateCell(row2, 6, Getcellstyle(book, "center", true, 11, 600), fine.ToString());
                    CreateCell(row2, 7, Getcellstyle(book, "center", true, 11, 600), "");
                }
            }

            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string fileName = HttpUtility.UrlEncode("考勤汇总表.xls", Encoding.GetEncoding("UTF-8"));
            if (list.Count > 0)
            {
                fileName = HttpUtility.UrlEncode(list[0].PA_UnitCode.TrimEnd() + "标" + list[0].PA_Date.ToString("yyy年MM月") + "考勤汇总表.xls", Encoding.GetEncoding("UTF-8"));
            }
            return File(ms, "application/vnd.ms-excel", fileName);
        }
        /// <summary>
        /// Excel导入
        /// </summary>
        /// <returns></returns>
        public JsonResult ImportExcelFile(HttpPostedFileBase FileData)
        {
            string Date = Request.QueryString["Date"];
            string Section = Request.QueryString["Section"];
            string infor = "";
            var isScuess = TryCatchCommand.TryExec(() =>
            { 
                if (FileData != null)
                {
                    string tempFileName = FileData.FileName;
                    string fileExt = Path.GetExtension(tempFileName).ToLower();
                    string fileName = Guid.NewGuid().ToString() + fileExt;
                    string tempPath = "/TempleterFilePath";
                    if (!tempPath.EndsWith("/"))
                        tempPath += "/";
                    tempPath = Server.MapPath(tempPath);
                    if (!Directory.Exists(tempPath))
                        Directory.CreateDirectory(tempPath);
                    tempPath = Path.Combine(tempPath, fileName);
                    FileData.SaveAs(tempPath);
                    if (string.IsNullOrWhiteSpace(tempPath) || !System.IO.File.Exists(tempPath))
                    {
                        throw new Exception("导入文件不存在");
                    }
                var list = PlmPersonnelattendanceUIRule.GetAllPersonnelAttendance("Convert(varchar,PA_Date,120) like'" + Date + "%' and  PA_UnitCode='" + Section + "'");
                if (list.Count > 0)
                {
                    bool delete = PlmPersonnelattendanceUIRule.DeleteBySql(Date, Section);
                }

                HSSFWorkbook hssfworkbook;
                //读取文件流
                using (FileStream fs = System.IO.File.OpenRead(tempPath))
                {
                    hssfworkbook = new HSSFWorkbook(fs);
                    //获取第一个工作簿
                    NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
                    {
                         //var ret = ExcelImportValidation.Validation<PlmPersonnelattendanceInfo>(sheet);
                         //if (ret.Item1)
                         //{
                             //获取第一行
                             IRow headerRow = sheet.GetRow(0);
                             //列数
                             int cellCount = headerRow.LastCellNum;
                             //行数
                             int rowCount = sheet.LastRowNum;
                             //循环行
                             for (int i = 2; i <= rowCount; i++)
                             {

                                 IRow row = sheet.GetRow(i);
                                 PlmPersonnelattendanceInfo info = new PlmPersonnelattendanceInfo();
                                 info.PA_ID = Guid.NewGuid().ToString();
                                 info.PA_UnitCode = Section;
                                 //ProjectUnitInfo uInfo = ProjectUnitUIRule.GetUnitInfoByCode(Section);
                                 //info.Se_ConstructionID = uInfo.UNIT_ID;
                                 //info.Se_ConstructionName = uInfo.UNIT_NAME;
                                 info.PA_Date = Convert.ToDateTime(Date);
                                 //操作人ID及名称 添加权限时赋值
                                 info.PA_UserID = 1;
                                 info.PA_UserName = "操作人名称";
                                 //读取该行信息
                                 info.PA_JobNum = GetCellValue(row.GetCell(0));
                                 info.PA_PersonnelName = GetCellValue(row.GetCell(1));
                                 info.PA_UnitName = GetCellValue(row.GetCell(2));
                                 info.PA_WorkDay = Convert.ToInt32(GetCellValue(row.GetCell(3)));
                                 info.PA_AttendanceDay = Convert.ToInt32(GetCellValue(row.GetCell(4)));
                                 info.PA_AbsenceDay = Convert.ToInt32(GetCellValue(row.GetCell(5)));
                                 info.PA_Remark = GetCellValue(row.GetCell(13));
                                 var leavelist = PlmPersonleaveUIRule.GetListBySql(info.PA_PersonnelName, info.PA_UnitCode, Date);
                                 if (leavelist.Count > 0)
                                 {
                                     if (leavelist[0].Pl_StartDate.Equals(leavelist[0].Pl_EndDate))
                                     {
                                         info.PA_LeaveRecord = leavelist[0].Pl_StartDate.ToString("yyyy年MM月dd日") + "请假1天";
                                         info.PA_LeaveDay = 1;
                                     }
                                     else
                                     {
                                         info.PA_LeaveRecord = leavelist[0].Pl_StartDate.ToString("yyyy年MM月dd日") + "到" + leavelist[0].Pl_EndDate.ToString("yyyy年MM月dd日") + ",共" + leavelist[0].Pl_EndDate.Subtract(leavelist[0].Pl_StartDate).Days.ToString() + "天";
                                         info.PA_LeaveDay = leavelist[0].Pl_EndDate.Subtract(leavelist[0].Pl_StartDate).Days;
                                     }
                                 }
                                 var personnellist = PlmPersonnelinfoUIRule.GetListBySql(info.PA_PersonnelName.Trim(), info.PA_UnitCode);
                                 if (personnellist.Count > 0)
                                 {
                                     info.PA_Post = personnellist[0].Pi_Post;
                                     info.PA_PersonnelID = personnellist[0].Pi_ID;
                                 }
                                 //向数据库插入数据
                                 PlmPersonnelattendanceUIRule.InsertEntity(info);
                             }
                         //}
                         //else
                         //{
                         //    infor = ret.Item2;
                         //}
                    }
                }
                //删除临时文件
                System.IO.File.Delete(tempPath);
                }
      
            });
            dynamic jsonReturn = new ExpandoObject();
            //保存成功
            if (isScuess)
            {
                //if (infor == "")
                //{
                    jsonReturn = new
                    {
                        statusCode = "200",
                        message = "导入成功",
                        navTabId = "",
                        rel = "",
                        callbackType = "",
                        forwardUrl = "",
                        confirmMsg = ""
                    };
                //}
                //else
                //{
                //    jsonReturn = new
                //    {
                //        statusCode = "300",
                //        message = "导入失败!  " + infor,
                //    };
                //}
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "导入失败",
                };
            }          
           
            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        //public JsonResult ImportExcelFile(int[] datas)
        //{
        //    string Date = Request.Form["Date"];
        //    string Section = Request.Form["Section"];
        //    string rownum = "";
        //    var isScuess = TryCatchCommand.TryExec(() =>
        //    {
        //        var list = PlmPersonnelattendanceUIRule.GetAllPersonnelAttendance("Convert(varchar,PA_Date,120) like'" + Date + "%' and  PA_UnitCode='" + Section + "'");
        //        if (list.Count > 0)
        //        {
        //            bool delete = PlmPersonnelattendanceUIRule.DeleteBySql(Date, Section);
        //        }

        //        HSSFWorkbook hssfworkbook;
        //        var bytes = datas.ToList().ConvertAll(x => (byte)x).ToArray();
        //        //读取文件流
        //        using (Stream file = new MemoryStream(bytes))
        //        {
        //            hssfworkbook = new HSSFWorkbook(file);
        //        }
        //        //获取第一个工作簿
        //        NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
        //        {
        //            //获取第一行
        //            IRow headerRow = sheet.GetRow(0);
        //            //列数
        //            int cellCount = headerRow.LastCellNum;
        //            //行数
        //            int rowCount = sheet.LastRowNum;
        //            //循环行
        //            for (int i = 2; i <= rowCount; i++)
        //            {
        //                try
        //                {
        //                    IRow row = sheet.GetRow(i);
        //                    PlmPersonnelattendanceInfo info = new PlmPersonnelattendanceInfo();
        //                    info.PA_ID = Guid.NewGuid().ToString();
        //                    info.PA_UnitCode = Section;
        //                    //ProjectUnitInfo uInfo = ProjectUnitUIRule.GetUnitInfoByCode(Section);
        //                    //info.Se_ConstructionID = uInfo.UNIT_ID;
        //                    //info.Se_ConstructionName = uInfo.UNIT_NAME;
        //                    info.PA_Date = Convert.ToDateTime(Date);
        //                    //操作人ID及名称 添加权限时赋值
        //                    info.PA_UserID = 1;
        //                    info.PA_UserName = "操作人名称";
        //                    //读取该行信息
        //                    info.PA_JobNum = GetCellValue(row.GetCell(0));
        //                    info.PA_PersonnelName = GetCellValue(row.GetCell(1));
        //                    info.PA_UnitName = GetCellValue(row.GetCell(2));
        //                    info.PA_WorkDay = Convert.ToInt32(GetCellValue(row.GetCell(3)));
        //                    info.PA_AttendanceDay = Convert.ToInt32(GetCellValue(row.GetCell(4)));
        //                    info.PA_AbsenceDay = Convert.ToInt32(GetCellValue(row.GetCell(5)));
        //                    info.PA_Remark = GetCellValue(row.GetCell(13));
        //                    var leavelist = PlmPersonleaveUIRule.GetListBySql(info.PA_PersonnelName, info.PA_UnitCode, Date);
        //                    if (leavelist.Count > 0)
        //                    {
        //                        if (leavelist[0].Pl_StartDate.Equals(leavelist[0].Pl_EndDate))
        //                        {
        //                            info.PA_LeaveRecord = leavelist[0].Pl_StartDate.ToString("yyyy年MM月dd日") + "请假1天";
        //                            info.PA_LeaveDay = 1;
        //                        }
        //                        else
        //                        {
        //                            info.PA_LeaveRecord = leavelist[0].Pl_StartDate.ToString("yyyy年MM月dd日") + "到" + leavelist[0].Pl_EndDate.ToString("yyyy年MM月dd日") + ",共" + leavelist[0].Pl_EndDate.Subtract(leavelist[0].Pl_StartDate).Days.ToString() + "天";
        //                            info.PA_LeaveDay = leavelist[0].Pl_EndDate.Subtract(leavelist[0].Pl_StartDate).Days;
        //                        }
        //                    }
        //                    var personnellist = PlmPersonnelinfoUIRule.GetListBySql(info.PA_PersonnelName.Trim(), info.PA_UnitCode);
        //                    if (personnellist.Count > 0)
        //                    {
        //                        info.PA_Post = personnellist[0].Pi_Post;
        //                        info.PA_PersonnelID = personnellist[0].Pi_ID;
        //                    }
        //                    //if (GetCellValue(row.GetCell(2)) != "")
        //                    //info.Se_EnterDate = Convert.ToDateTime(GetCellValue(row.GetCell(2)));
        //                    //向数据库插入数据

        //                    PlmPersonnelattendanceUIRule.InsertEntity(info);
        //                }
        //                catch (Exception)
        //                {
        //                    rownum += i + ",";

        //                }

        //            }
        //        }
        //    });
        //    dynamic jsonReturn = new ExpandoObject();
        //    //保存成功
        //    if (isScuess)
        //    {
        //        if (rownum.Length > 0)
        //        {
        //           rownum= rownum.TrimEnd(',');
        //            jsonReturn = new
        //            {
        //                statusCode = "200",
        //                message = "第" + rownum + "行导入失败",
        //                navTabId = "",
        //                rel = "",
        //                callbackType = "",
        //                forwardUrl = "",
        //                confirmMsg = ""
        //            };
        //        }
        //        else
        //        {
        //            jsonReturn = new
        //            {
        //                statusCode = "200",
        //                message = "导入成功",
        //                navTabId = "",
        //                rel = "",
        //                callbackType = "",
        //                forwardUrl = "",
        //                confirmMsg = ""
        //            };
        //        }

        //    }
        //    else
        //    {
        //        if (rownum.Length > 0)
        //        {
        //            rownum.TrimEnd(',');
        //            jsonReturn = new
        //            {
        //                statusCode = "300",
        //                message = "第" + rownum + "行导入失败",
        //            };
        //        }
        //        else
        //        {
        //            jsonReturn = new
        //            {
        //                statusCode = "300",
        //                message = "导入失败",
        //            };
        //        }

        //    }
        //    return new JsonResult()
        //    {
        //        Data = jsonReturn
        //    };
        //}
        #endregion



    }
}
